import pymysql
from Util.Return import *
import json


# 替换以下信息为你的MySQL数据库信息
def connect():  # 创建数据库连接
    conn = pymysql.connect(
        host=get_config("config", "database")['host'],
        port=get_config("config", "database")['port'],
        user=get_config("config", "database")['user'],
        password=get_config("config", "database")['password'],
        database=get_config("config", "database")['database']
    )
    # 创建游标对象
    cursor = conn.cursor()
    return conn, cursor


def close(conn, cursor):  # 关闭数据库连接
    cursor.close()
    conn.close()


def select(sql_string):  # 执行select命令
    conn, cursor = connect()  # 执行数据库连接
    cursor.execute(sql_string)  # 执行SQL查询
    result = cursor.fetchall()  # 获取查询结果
    column = [desc[0] for desc in cursor.description]   # 获得字段名
    close(conn, cursor)  # 关闭数据库连接
    result_with_column = []
    for row in result:  # 以字典的格式返回
        row_dict = dict(zip(column, row))
        result_with_column.append(row_dict)
    return result_with_column


def insert(json_list, table):  # 将一个json字符串列表中的内容插入到表中
    conn, cursor = connect()  # 执行数据库连接
    try:
        for json_data in json_list:
            columns = ', '.join(json_data.keys())
            values = ', '.join([f'"{value}"' for value in json_data.values()])
            insert_query = f"INSERT IGNORE INTO {table} ({columns}) VALUES ({values})"  # 构建插入语句
            cursor.execute(insert_query)
            # 提交更改
            conn.commit()
        return True
    except Exception as e:
        # 处理插入失败的情况
        conn.rollback()
        print(f"插入失败: {str(e)}")
        return False
    finally:
        close(conn, cursor)  # 关闭数据库连接


def update(json_data, condition, table):  # 将一个json字符串列表中的内容修改到表中
    conn, cursor = connect()  # 执行数据库连接
    try:
        update_info = ', '.join([f'{key} = "{value}"' for key, value in json_data.items()])
        update_query = f"UPDATE {table} SET {update_info} WHERE {condition}"  # 构建修改语句
        cursor.execute(update_query)
        # 提交更改
        conn.commit()
        return True
    except Exception as e:
        # 处理插入失败的情况
        conn.rollback()
        print(f"修改失败: {str(e)}")
        return False
    finally:
        close(conn, cursor)  # 关闭数据库连接


def delete(condition, table):  # 将表中的进行删除
    conn, cursor = connect()  # 执行数据库连接
    try:
        delete_query = f"DELETE FROM {table} WHERE {condition}"  # 构建删除语句
        cursor.execute(delete_query)
        # 提交更改
        conn.commit()
        return True
    except Exception as e:
        # 处理插入失败的情况
        conn.rollback()
        print(f"删除失败: {str(e)}")
        return False
    finally:
        close(conn, cursor)  # 关闭数据库连接
